package com.tdcoding.chat.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSession;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.tdcoding.chat.configure.Configuration;
import com.tdcoding.chat.helper.Logger;
import com.tdcoding.chat.helper.StringHelper;

/**
 * 
 * @Description: 数据库连接池管理类，单例
 * 
 */
public class DBManager {
	private static final String TAG="DBManager";
	
	// 连接池集合
	private static ConcurrentHashMap<String, DataSource> mDataSources;
	
	private static ArrayList<String> dbNames;
	
	private static SqlSession sqlSession;
	
	public static void initDBManager(Configuration configuration) throws Exception {
		dbNames=new ArrayList<String>();
		mDataSources = new ConcurrentHashMap<String, DataSource>();
		sqlSession = configuration.configDBMapping();
	}

	public static SqlSession getSqlSession() {
		return sqlSession;
	}
	
	public static boolean doCommit( ) {
		if (sqlSession==null) {
			return false;
		}
		sqlSession.commit();
		return true;
	}

	/**
	 * 初始化加载
	 * @throws Exception 
	 */
	private static void initDataSource(Properties dbProperties) throws Exception {
		String dbName=dbProperties.getProperty("dbname");
		dbProperties.setProperty("url", dbProperties.getProperty("url")+dbName);
		Logger.println(TAG, "initDataSource", dbName,dbProperties.getProperty("url"));
		DataSource dataSource = DruidDataSourceFactory.createDataSource(dbProperties);
		mDataSources.put(dbName, dataSource);
		dbNames.add(dbName);
	}

	/**
	 * 插入数据
	 * @param dbIndex
	 * @param table
	 * @param cloumns
	 * @param values
	 * @throws SQLException
	 */
	public static int insertSql(int dbIndex,String table, String columns,Object... values) {
		String sql = "insert into " + table +"("+columns+") values(" + getValues(values)+ ")";
		Logger.println(TAG, "insertSql", sql);
		Connection conn = null;
		try {
			conn = getConnection(dbIndex);
			PreparedStatement statement = conn.prepareStatement(sql);
			return statement.executeUpdate()==1?0:-1;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (conn!=null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}

		return -1;
	}
	
	private static String getValues(Object... values){
		StringBuffer sqlBuffer = new StringBuffer();
		for (Object value : values) {
			if (value==null||value.equals("")) {
				sqlBuffer.append("null,");
			}
			else if (value instanceof Integer
					||value instanceof Float
					||value instanceof Long
					||value instanceof Double) {
				sqlBuffer.append(value + ",");
			}
			else if (value instanceof String) {
				sqlBuffer.append("'" + value + "',");
			}
		}
		String svalues=sqlBuffer.toString();
		Logger.println(TAG, "getValues", svalues);
		return svalues;
	}
	
	/**
	 * 删除语句
	 * 
	 * @param dbIndex
	 * @param table
	 * @param where
	 * @throws SQLException
	 */
	public static int deleteSql(int dbIndex,String table, String where){
		String sql = "delete from " + table;
		if (StringHelper.hasValue(where)) {
			sql += " where " + where;
		}
		Connection conn = null;
		try {
			conn = getConnection(dbIndex);
			PreparedStatement statement = conn.prepareStatement(sql);
			int result=statement.executeUpdate();
			return result>0?0:-1;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (conn!=null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return -1;
	}

	/**
	 * 参数查询
	 * @param dbIndex
	 * @param table
	 * @param columns
	 * @param where
	 * @param orderBy
	 * @param groupBy
	 * @throws SQLException
	 */
	public static ResultSet querySql(int dbIndex,String table, String columns, String where,
			String orderBy, String groupBy, String limit) throws SQLException {
		String sql = "";
		if (!StringHelper.hasValue(columns)) {
			columns = "*";
		}
		sql = "select " + columns + " from " + table;
		if (StringHelper.hasValue(where)) {
			sql += " where " + where;
		}
		if (StringHelper.hasValue(orderBy)) {
			sql += " order by " + orderBy;
		}
		if (StringHelper.hasValue(groupBy)) {
			sql += " group by " + groupBy;
		}
		if (StringHelper.hasValue(limit)) {
			sql += " limit " + limit;
		}
		Logger.println(TAG, "querySql",dbIndex, sql);
		return querySql(dbIndex, sql);
	}

	/**
	 * 按照sql查询
	 * @param dbIndex
	 * @param sql
	 * @throws SQLException
	 */
	public static ResultSet querySql(int dbIndex,String sql) throws SQLException {
		if (!StringHelper.hasValue(sql)) {
			return null;
		}
		Connection conn =getConnection(dbIndex);
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		return resultSet;
	}

	/**
	 * 更新
	 * 
	 * @param table
	 * @param set
	 * @param where
	 * @throws SQLException
	 */
	public static int updateSql(int dbIndex,String table, String set, String where) {
		String sql = "update " + table + " set " + set;
		if (StringHelper.hasValue(where)) {
			sql += " where " + where;
		}
		Connection conn = null;
		try {
			conn = getConnection(dbIndex);
			PreparedStatement statement = conn.prepareStatement(sql);
			return statement.executeUpdate()>0?0:-1;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (conn!=null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return -1;
	}

	/**
	 * 创建表
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static boolean createTableSql(int dbIndex,String sql) throws SQLException {
		if (!StringHelper.hasValue(sql)) {
			return false;
		}
		Connection conn =getConnection(dbIndex);
		Statement stmt = conn.createStatement();
		boolean result = stmt.execute(sql);
		return result?false:true;
	}

	protected static boolean dropTable(int dbIndex,String table) throws SQLException{
		String sql="drop table " + table;
		Connection conn =getConnection(dbIndex);
		Statement stmt = conn.createStatement();
		boolean result= stmt.execute(sql);
		return result?false:true;
	}

	/**
	 * 
	 * @Description: 获得DB连接
	 * @return 指定的链接池中的一个连接
	 * @throws SQLException 
	 */
	private static Connection connection;
	public static Connection getConnection(int dbIndex) throws SQLException {
		String dbName=dbNames.get(dbIndex);
		DataSource dataSource = mDataSources.get(dbName);
		if (dataSource != null&&(connection==null||connection.isClosed())) {
			connection = dataSource.getConnection();
		}
		return connection;
	}

	/**
	 * @Description: 释放连接
	 * @param resultSet
	 */
	public static void closeConnection(ResultSet resultSet) {
		try {
			if (resultSet.getStatement()!=null) {
				closeConnection(resultSet.getStatement().getConnection());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @Description: 释放连接
	 * @param dbIndex
	 *            连接所在的连接池
	 */
	public static void closeConnection(int dbIndex) {
		try {
			closeConnection(getConnection(dbIndex));
		} catch (SQLException e) {
			e.printStackTrace();
			Logger.println(TAG, "closeConnection err", e);
		}
	}
	
	/**
	 * 关闭所有连接池
	 * @throws SQLException 
	 */
	public static void closeDataSource() throws SQLException {
		if (mDataSources.size() > 0) {
			Iterator<String> iterator = mDataSources.keySet().iterator();
			while (iterator.hasNext()) {
				String dbName=iterator.next();
				DataSource dataSource= mDataSources.remove(dbName);
				if (dataSource != null) {
					closeConnection(dataSource.getConnection());
				}
			}
			mDataSources.clear();
		}
	}
	
	/**
	 * 关闭连接
	 * @param connection
	 *            
	 */
	public static void closeConnection(Connection connection) {
		try {
			if (connection!=null&&!connection.isClosed()) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			Logger.println(TAG, "closeConnection err", e);
		}
	}
	
	/**
	 * 关闭Mybatis连接
	 *            
	 */
	public static void closeMybatis() {
		if (sqlSession!=null) {
			sqlSession.commit();
			sqlSession.clearCache();
			sqlSession.close();
		}
	}
	
//	public static void main(String[] args) {
//		DBManager.getInstance();
//	}
}
